package com.alpha.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.log4j.Logger;

import com.alpha.bom.Student;
import com.alpha.dao.interfaces.IStudentDao;

public class StudentDao extends AlphaDao implements IStudentDao{
	private Connection connection;
	private Statement statement;
	private PreparedStatement preparedStatement;
	private ResultSet resultSet;
	private static Logger logger = Logger.getLogger(StudentDao.class.getName());
	
	public void connect() throws NamingException, SQLException {
			try {
				Context context= new InitialContext();
				DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/alpha");			
				connection = ds.getConnection();
			} catch (NamingException e) {
				logger.error(e.getMessage());
				throw e;
			} catch (SQLException e) {
				logger.error(e.getMessage());
				throw e;
			}
	}
	
	public void disconnect() throws SQLException {
		if(this.resultSet != null) {
			try {
				this.resultSet.close();
			} catch (SQLException e) {
				logger.error(e.getMessage());
				throw e;
			}
		}
		if(this.preparedStatement != null) {
			try {
				this.preparedStatement.close();
			} catch (SQLException e) {
				logger.error(e.getMessage());
				throw e;
			}
		}
		if(this.statement != null) {
			try {
				this.statement.close();
			} catch (SQLException e) {
				logger.error(e.getMessage());
				throw e;
			}
		}
		if(this.connection != null) {
			try {
				this.connection.close();
			} catch (SQLException e) {
				logger.error(e.getMessage());
				throw e;
			}
		}
	}
	
	public static void main(String[] args) throws SQLException, NamingException {
		StudentDao s = new StudentDao();
		s.connect();
	}
	
	public Student retrieveStudent(int studentId) throws SQLException, NamingException {
		Student student = null;
		this.connect();
		try {
			String select = "SELECT * FROM tbl_user INNER JOIN tbl_student ON tbl_user.user_id=tbl_student.student_id WHERE student_id="+studentId;
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				int userId = this.resultSet.getInt("user_id");
				String password = this.resultSet.getString("user_password");
				String firstName = this.resultSet.getString("user_first_name");
				String lastName = this.resultSet.getString("user_last_name");
				boolean activated = this.resultSet.getInt("student_activated") == 1 ? true : false;
				student = new Student(userId, firstName, lastName, activated);
				student.setUserId(userId);
				student.setPassword(password);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally{
			this.disconnect();
		}

		return student;
	}
	
	public ArrayList<Student> retrieveStudentsByProfessorId(int professorId) throws SQLException, NamingException {
		ArrayList<Student> students = new ArrayList<Student>();
		this.connect();
		try {
			String select = "SELECT * FROM tbl_user INNER JOIN tbl_student ON tbl_user.user_id=tbl_student.student_id";
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				int userId = this.resultSet.getInt("user_id");
				String password = this.resultSet.getString("user_password");
				String firstName = this.resultSet.getString("user_first_name");
				String lastName = this.resultSet.getString("user_last_name");
				boolean activated = this.resultSet.getInt("student_activated") == 1 ? true : false;
				Student student = new Student(userId, firstName, lastName, activated);
				student.setUserId(userId);
				student.setPassword(password);
				students.add(student);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
		} finally {
			this.disconnect();
		}
		
		return students;
	}
	
	public int retrieveNextUserId() throws SQLException {
		int userId = 0;
		try {
			String select = "SELECT MAX(user_id)+1 FROM tbl_user";
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				userId = this.resultSet.getInt(1);
				userId = userId > 0 ? userId : 1;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} 
		
		return userId;
	}
	
	public boolean insertStudent(Student student, int professorId) throws SQLException, NamingException {
		boolean success = false;
		this.connect();
		String insert1 = "INSERT INTO tbl_user(user_id, user_password, user_first_name, user_last_name, user_is_admin) VALUES (?,?,?,?,?)";
		String insert2 = "INSERT INTO tbl_student(student_id, student_activated, professor_id) VALUES (?,?,?)";
		String insert3 = "INSERT INTO tbl_user_role(user_id, role_name) VALUES (?,?)";
		try {
			int id = this.retrieveNextUserId();
			this.preparedStatement = this.connection.prepareStatement(insert1);
			this.preparedStatement.setInt(1, id);
			this.preparedStatement.setString(2, student.getPassword());
			this.preparedStatement.setString(3, student.getFirstName());
			this.preparedStatement.setString(4, student.getLastName());
			this.preparedStatement.setInt(5, 0);
			int rows = this.preparedStatement.executeUpdate();
			
			this.preparedStatement = this.connection.prepareStatement(insert2);
			this.preparedStatement.setInt(1, id);
			this.preparedStatement.setInt(2, student.isActivated() ? 1 : 0);
			this.preparedStatement.setInt(3, professorId);
			rows += this.preparedStatement.executeUpdate();
			
			this.preparedStatement = this.connection.prepareStatement(insert3);
			this.preparedStatement.setInt(1, id);
			this.preparedStatement.setString(2, "user");
			rows += this.preparedStatement.executeUpdate();
			
			if(rows > 0) {
				success = true;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean updateStudent(Student student) throws SQLException, NamingException {
		boolean success = false;
		this.connect();
		String update = "UPDATE tbl_student LEFT JOIN tbl_user ON tbl_student.student_id=tbl_user.user_id SET user_first_name=?, user_last_name=?, user_password=?, student_activated=? WHERE student_id=?";
		try {
			this.preparedStatement = this.connection.prepareStatement(update);
			this.preparedStatement.setString(1, student.getFirstName());
			this.preparedStatement.setString(2, student.getLastName());
			this.preparedStatement.setString(3, student.getPassword());
			this.preparedStatement.setInt(4, student.isActivated() ? 1 : 0);
			this.preparedStatement.setInt(5, student.getUserId());
			int rows = this.preparedStatement.executeUpdate();
			if(rows > 0) {
				success = true;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return success;
	}
}
